<?php
/***
 * ActionLeaf (www.actionleaf.com / actionleaf.googlecode.com) PHP Framework
 * Copyright (c) 2008, Wess D. Cope <wess@wattz.net>
 * All rights reserved.								
 * 													
 * Redistribution and use in source and binary forms, with or without modification,
 * are permitted provided that the following conditions are met:                   
 * 																				
 * 	* Redistributions of source code must retain the above copyright notice,	
 * 	this list of conditions and the following disclaimer.						
 * 																				
 * 	* Redistributions in binary form must reproduce the above copyright notice, 
 * 	this list of conditions and the following disclaimer in the documentation   
 * 	and/or other materials provided with the distribution. 						
 * 																				
 * 	* Neither the name of the <ORGANIZATION> nor the names of its contributors may
 * 	be used to endorse or promote products derived from this software without     
 * 	specific prior written permission.											
 * 																				
 * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND ANY 	 
 * EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES  
 * OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT 	 
 * SHALL THE COPYRIGHT HOLDER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, 		 
 * INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED  
 * TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR 	 
 * BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN 	 
 * CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN 	 
 * ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH 	 
 * DAMAGE.																				 
 **/

class ALDataObject extends ALData
{
	private $fields = array();
	private $fieldAssoc = array();
	private $clearedFields = array();
	private $table;
	private $primaryKey = null;
	private $dbType;

	// Query Properties
	private $queryFilters = array();
	private $querySortBy = array();
	private $queryLimit=0;
	private $queryOffset=0;

	// Join Properties
	private $relationTable=null;
	private $relationTablePrimaryKeyField=0;
	private $relationTableForgeinKeyField=null;
	private $relationTableLocalKeyField=null;
	private $relationType=0;	// 1 = One to One, 2 = One to Many
	private $relationName=null;
	private $relationTableFields=array();
	private $relationQueryFilters = array();
	
	/**
	 * Pull database connection and build fields from table column description.  If fieldVals is
	 * passed then the fields and fieldAssoc properties are set  
	 *
	 * @param	string
	 * @param	string
	 * @param	array
	 * @param	string		This is the field name of the primary key
	 **/
	public function __construct($connectionName, $table, $fieldVals=NO, $primaryKey=NO)
	{

		if(!$connectionName||!$table)
			die("No Connection name and/or table name given");

		parent::__construct($connectionName);

		$this->table = $table;

		// Build fields/fieldAssoc
		if (empty($fieldVals) && empty($primaryKey)) {
			$this->describeTable();
		} else {
			$this->fields = array_keys($fieldVals);
			$this->fieldAssoc = $fieldVals;
			$this->clearedFields = $this->fields;
			if (isset($this->fieldAssoc[$primaryKey])) {
				$this->primaryKey = $primaryKey;
			} else {
				die('ALDataObject::__construct Error: fieldVals does not contain a field key that matches the passed primaryKey'.$primaryKey);
			}
		}

	}

	/**
	 * Return a single column value.
	 *
	 * @param	string		This is the 
	 **/
	public function __get($var)
	{
		return $this->fieldAssoc[$var];
	}

	/**
	 * Set a single field value.
	 *
	 * @param	string
	 * @param	string
	 **/
	public function __set($var, $val)
	{
		if($this->fieldAssoc[$var] == $this->primaryKey)
			die("You cannot set the value of a primary key.");
		else
			$this->fieldAssoc[$var] = $val;
	}

	/**
	 * Builds internal table fields array and primary key using a sql DESCRIBE query.
	 *
	 * @return	null
	 **/
	private function describeTable()
	{

		$res = $this->currentConnection->query("DESCRIBE " . $this->table);
		if($res) {
			$resultSet = $res->fetchAll(PDO::FETCH_OBJ);
			foreach($resultSet as $f) {
				if($f->Key == "PRI")
					$this->primaryKey = $f->Field;
				$this->fieldAssoc[$f->Field] = null;	
			}
			$this->clearedFields = array_keys($this->fieldAssoc);
		}

	}
	
	/**
	 * Build internal join table fields array and prmary key using a sql DESCRIBE query.
	 *
	 * @return	null
	 **/
	private function describeRelationTable()
	{

		$res = $this->currentConnection->query("DESCRIBE " . $this->relationTable);
		if($res) {
			$resultSet = $res->fetchAll(PDO::FETCH_OBJ);
			foreach($resultSet as $f) {
				if($f->Key == "PRI")
					$this->relationTablePrimaryKeyField = $f->Field;
				$this->relationTableFields[] = $f->Field;
			}
		}

	}

	/**
	 * Build SQL query based off set query properties. These are set using the chaining functions
	 * such as ->sortBy(), ->limit(), etc..
	 *
	 * @return	string
	 **/
	public function getSQL()
	{

		$columns = '`'.implode('`, `',$this->clearedFields).'`';
		$where = count($this->queryFilters)?"\nWHERE\n\t".implode(" AND\n\t", $this->queryFilters):'';
		$sortBy = count($this->querySortBy)?"\nORDER BY\n\t".implode(", ", $this->querySortBy):'';
		$limit = $this->queryLimit?"\nLIMIT ".$this->queryLimit:'';
		$limit = ($this->queryLimit&&$this->queryOffset)?$limit." OFFSET {$this->queryOffset}":$limit;

		$sql =	"SELECT\n\t".$columns."\nFROM\n\t".$this->table.$where.$sortBy.$limit;

		return $sql;

	}

	/**
	 * Build SQL query based off join table properties and foreign keys supplied
	 *
	 * @param	array
	 *
	 * @return	string
	 **/
	public function getRelationTableSQL($primaryKeys,$foreignKeys=false)
	{

		// Set forgein key to be distinct for results when a 1to1 relation
		$columns = '`'.implode('`, `',$this->relationTableFields).'`';
		$groupBy = '';
		if ($foreignKeys) {
			$where = "\nWHERE\n\t".$this->relationTableForgeinKeyField." IN (".implode(',',$foreignKeys).")";
		} else {
			$where = "\nWHERE\n\t".$this->relationTableForgeinKeyField." IN (".implode(',',$primaryKeys).")";
			$groupBy = ($this->relationType<2)?"\nGROUP BY\n\t".$this->relationTableForgeinKeyField:'';
		}
		$where = count($this->relationQueryFilters) ? $where . ' AND ' . implode(" AND\n\t", $this->relationQueryFilters) : $where;

		$sql = "SELECT\n\t".$columns."\nFROM\n\t".$this->relationTable.$where.$groupBy;

		return $sql;

	}

	/*****************
	 * RETURN METHODS
	 *****************/

	/**
	 * Return query results.  If there is a table relationship then return that too.
	 *
	 * @return	array
	 **/
	private function getResults()
	{

		$returnSet=null;
		$resultsPKs=array();
		$resultsFKs=array();
		$result = $this->query($this->getSQL());

		if ($result && count($result)) {

			foreach($result as $value) {
				$returnSet[]=$this->buildALResultObject($value);
				if ($this->relationType) {
					if (!in_array($value->{$this->primaryKey},$resultsPKs) && !empty($value->{$this->primaryKey}))
						$resultsPKs[]=$value->{$this->primaryKey};
					if ($this->relationTableLocalKeyField)
						if (!in_array($value->{$this->relationTableLocalKeyField},$resultsFKs)  && !empty($value->{$this->relationTableLocalKeyField}))
							$resultsFKs[]=$value->{$this->relationTableLocalKeyField};
				}
			}

			// Build relation query and results if needed
			if ($this->relationType && count($resultsPKs)) {
				$this->describeRelationTable();
				$relationResult = $this->query($this->getRelationTableSQL($resultsPKs,$resultsFKs));
				foreach($relationResult AS $relationRow) {
					foreach($returnSet AS $returnItem) {
						if ($this->relationTableLocalKeyField) {
							// Return relation of a table with a forgein key in base table
							if ($returnItem->{$this->relationTableLocalKeyField} == $relationRow->{$this->relationTableForgeinKeyField}) {
								$returnItem->addRelationItem($this->buildALResultObject($relationRow,true));
							}
						} else {
							// Return relation of a table with a forgein key in relation table
							if ($returnItem->{$this->primaryKey} == $relationRow->{$this->relationTableForgeinKeyField}) {
								$returnItem->addRelationItem($this->buildALResultObject($relationRow,true));
							}
						}
					}
				}
			}

		}
		
		// Reset Query Properties
		$this->queryFilters = array();
		$this->querySortBy = array();
		$this->queryLimit=0;
		$this->queryOffset=0;
		// Reset Relationship Properties
		$this->relationTable=null;
		$this->relationTablePrimaryKeyField=null;
		$this->relationTableForgeinKeyField=null;
		$this->relationTableLocalKeyField=null;
		$this->relationType=0;	// 1 = One to One, 2 = One to Many
		$this->relationName=null;
		$this->relationTableFields=array();

		return $returnSet;

	}
	
	/**
	 * Build new ALResultObject from a PDQ standard class response row.
	 *
	 * @param	object
	 *
	 * @return	object
	 **/
	private function buildALResultObject($rowObj,$isRelationTable=false)
	{

		$tableName = ($isRelationTable)?$this->relationTable:$this->table;
		$primaryKey = ($isRelationTable)?$this->relationTablePrimaryKeyField:$this->primaryKey;
		return new ALResultObject($this->connectionName, $tableName, (array)$rowObj, $primaryKey, $this->relationName, $this->relationType);

	}

	/**
	 * Return a single result
	 *
	 * @param	string		You can enter unlimted amount of where clauses as params
	 *
	 * @return	object		ALResultObject
	 **/
	public function get()
	{

		$this->queryFilters=func_get_args();
		$this->queryLimit = 1;
		
		return $this->getResults();

	}

	/**
	 * Return all results.  If there is a table relationship then return that too.
	 *
	 * @return	array
	 **/
	public function all()
	{

		return $this->getResults();

	}

	/*****************************
	 * SET QUERY PROPERTIES
	 *****************************/

	/**
	 *	Set the columns to be pulled from table to not include the passed in fields
	 *
	 * @param	string
	 *
	 * @return	object		Returns self object to allow chaining 
	 **/
	public function except($fieldLists)
	{

		if(!empty($fieldLists)) {
			$exceptFields = explode(",", str_replace(" ", "", $fieldLists));
		} else {
			die("No field list provided");
		}

		$fields = array_keys($this->fieldAssoc);

		$this->clearedFields = array_diff($fields, $exceptFields);

		return $this;

	}

	/**
	 *	Set the fields to be pulled from table to these fields exclusively
	 *
	 *	@param	string
	 *
	 * @return	object		Returns self object to allow chaining 
	 **/
	public function only($fieldList)
	{
		if(!empty($fieldList))
			$this->clearedFields = explode(",", str_replace(" ", "", $fieldList));
		else
			die("No field list provided");

		return $this;
	}

	/**
	 * Set where clauses for our sql query.  Params passed in will be joined with an AND.
	 *
	 * @param	string		You can enter unlimted amount of where clauses as params
	 *
	 * @return	null
	 **/
	public function filter()
	{
		$this->queryFilters=func_get_args();
		return $this;
	}

	/**
	 * Set where clauses for our relationship sql query.  Params passed in will be joined with an AND.
	 *
	 * @param	string		You can enter unlimted amount of where clauses as params
	 *
	 * @return	null
	 **/
	public function joinFilter()
	{
		$this->relationQueryFilters = func_get_args();
		return $this;
	}

	/**
	 * Set sort by fields.  This is a comma delimited list of columns to sort by.  The order you
	 * put these fields in is the order in which they are sdet inside the actual sql query. You
	 * can prefix the column name with a - (dash) to set DESC.
	 *
	 * @param	string
	 *
	 * @return	null
	 **/
	public function sortBy($sortBy)
	{

		$this->querySortBy=array();
		$columns = explode(',',$sortBy);
		foreach($columns AS $column) {
			$column=trim($column);
			$this->querySortBy[]=(strpos($column,'-')===false)?$column:str_replace('-','',$column).' DESC';
		}

		return $this;

	}
	
	/**
	 * Set the limit of number of result set
	 *
	 * @param	int
	 *
	 * @return	object		Returns self object to allow chaining
	 **/
	public function limit($limit)
	{
		$this->queryLimit = (int)$limit;
		
		return $this;
	}
	
	/**
	 * Set the offset of the result set
	 *
	 * @param	int
	 *
	 * @return	object		Returns self object to allow chaining
	 **/
	public function offset($offset)
	{
		$this->queryOffset = (int)$offset;
		
		return $this;
	}

	/**************************************
	 * SET DATABSE RELATIONSHIP PROPERTIES
	 **************************************/
	
	/**
	 * Setup the one-to-one database relationship to be executed on return
	 *
	 * @param	string
	 * @param	string
	 * @param	string		This is the optional name for your relationship.  This is used when
	 * 						accessing the relationships result set.  If no relationship name is
	 * 						provided then the table name is used.
	 * @param	
	 *
	 * @return	object		Returns self object to allow chaining 
	 **/
	public function oneToOne($relationTable, $relationTableForgeinKeyField, $relationName=false, $relationTableLocalKeyField=false) {

		$this->relationName = ($relationName)?$relationName:$relationTable;
		$this->relationTable = $relationTable;
		$this->relationTableForgeinKeyField = $relationTableForgeinKeyField;
		$this->relationTableLocalKeyField = $relationTableLocalKeyField;
		$this->relationType = 1;

		return $this;

	}
	
	/**
	 * Setup the one-to-many database relationship to be executed on return
	 *
	 * @param	string
	 * @param	string
	 * @param	string		This is the optional name for your relationship.  This is used when
	 * 						accessing the relationships result set.  If no relationship name is
	 * 						provided then the table name is used.
	 *
	 * @return	object		Returns self object to allow chaining 
	 **/
	public function oneToMany($relationTable, $relationTableForgeinKeyField, $relationName=false) {

		$this->relationName = ($relationName)?$relationName:$relationTable;
		$this->relationTable = $relationTable;
		$this->relationTableForgeinKeyField = $relationTableForgeinKeyField;
		$this->relationType = 2;

		return $this;

	}

}